#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : optimized_sql_query.py
@Author : sheen
@time : 2025/05/20 11:45
@func : 优化版SQL查询工具
"""

import logging
import time
import json
import oracledb
from typing import List, Dict, Any
from com.cn.for_cdc.common.cdc_conmons import oracle_adm_connections, oracle_siebel_connections
from com.cn.for_cdc.common.log_helper import configure_logging


def execute_queries(conn: oracledb.Connection, sql_statements: List[str]) -> None:
    """执行SQL查询并记录性能指标"""
    for sql in sql_statements:
        logging.info(f"开始执行SQL查询:\n {sql}")
        try:
            start_sql = time.time()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                # 格式化输出结果
                columns = [col[0] for col in cursor.description]
                rows = [dict(zip(columns, row)) for row in cursor]
                elapsed = time.time() - start_sql
                logging.info(f"执行耗时: {elapsed:.2f}s,结果:\n{json.dumps(rows, indent=2)}")
        except oracledb.DatabaseError as e:
            logging.error(f"SQL执行失败: {e}\nSQL:\n{sql}")
            raise


def check_sql_queries(config: Dict[str, Any], sql_statements: List[str]) -> None:
    """通过连接池执行查询"""
    try:
        # 初始化连接池
        pool = oracledb.SessionPool(
            user=config["user"],
            password=config["password"],
            dsn=config["dsn"],
            min=2,
            max=10,
            increment=2,
            encoding="UTF-8"
        )
        with pool.acquire() as conn:
            execute_queries(conn, sql_statements)
    except Exception as e:
        logging.error(f"数据库连接失败: {e}")
        raise


def main():
    """主函数"""
    configure_logging()
    config = oracle_adm_connections['icibe_pro']
    # config = oracle_siebel_connections['icibe_pro']
    logging.info("开始执行SQL查询...")
    logging.info(config)
    query_sql_list = [


    """
    
    
SELECT /*+ parallel(16) */ ROW_ID AS contact_id,NVL(X_SUPP_TP_INT_FLG,'N')
FROM siebel.s_contact@SBL sc
WHERE sc.BU_ID = '1-7V7V'
AND sc.LAST_UPD < sysdate -1 / 12
MINUS 
SELECT /*+ parallel(16) */sbc.CONTACT_ID,NVL(sbc.SUPP_TP_INT_FLG ,'N')
FROM STG_B_CONTACT sbc

    """

    ]

    start = time.time()
    check_sql_queries(config, query_sql_list)
    logging.info(f"总耗时: {time.time() - start:.2f}s")


if __name__ == "__main__":
    main()